5 - The Many-to-Many Relationship

Answers to exercises

:::

::::::::::::::::::::::::::::::::::::::::::::::::::::: {style=“text-align:left”} ::: question 1.

Draw data models for the following situations. In each case, think about the names you give each entity: :::

1a.

Farmers can own cows or share cows with other farmers.

1c.

A patient can have many physicians, and a physician can have many patients.

1e.

The Marathoner, a monthly magazine, regularly reports the performance of professional marathon runners. It has asked you to design a database to record the details of all major marathons (e.g., Boston, London, and Paris). Professional marathon runners compete in several races each year. A race may have thousands of competitors, but only about 200 or so are professional runners, the ones The Marathoner tracks. For each race, the magazine reports a runner's time and finishing position and some personal details such as name, gender, and age.

CREATE TABLE player (
   playerno SMALLINT,
   pfname    VARCHAR(15),
   plname    VARCHAR(20),
   pdob      DATE,
     PRIMARY KEY (playerno));
CREATE TABLE competition (
   compno    SMALLINT,
   location  VARCHAR(20),
   comptitle VARCHAR(20),  
   datebegin DATE,  
      PRIMARY KEY (compno));
CREATE TABLE field (  
   playerno  SMALLINT,  
   compno    SMALLINT,  
     PRIMARY KEY (playerno, compno),  
     CONSTRAINT fkplayer1 FOREIGN KEY(playerno) REFERENCES player(playerno),  
     CONSTRAINT fkcomp1 FOREIGN KEY(compno) REFERENCES competition(compno)); 
CREATE TABLE golfround(  
   roundno   SMALLINT,  
   rounddate DATE,  
   compno    SMALLINT,  
     PRIMARY KEY (roundno, compno),  
     CONSTRAINT fkcomp2 FOREIGN KEY(compno) REFERENCES competition(compno));
CREATE TABLE hole ( 
   holeno    SMALLINT,  
   length    SMALLINT,  
   par       SMALLINT,  
   roundno   SMALLINT,  
   compno    SMALLINT,  
     PRIMARY KEY (holeno, roundno, compno),  
     CONSTRAINT fkround FOREIGN KEY(roundno, compno) REFERENCES golfround(roundno, compno));
CREATE TABLE score ( 
   strokes   SMALLINT,  
   holeno    SMALLINT,  
   roundno   SMALLINT,  
   compno    SMALLINT,  
   playerno  SMALLINT,  
     PRIMARY KEY (holeno, roundno, compno, playerno),  
     CONSTRAINT fkhole FOREIGN KEY(holeno, roundno, compno) REFERENCES hole(holeno, roundno, compno), 
     CONSTRAINT fkplayer2 FOREIGN KEY(playerno) REFERENCES player(playerno)); 

3.

Write the following SQL queries for the database described in this chapter:

3a.

List the names of items for which the quantity sold is greater than one for any sale.

SELECT itemname FROM item JOIN lineitem
ON item.itemno = lineitem.itemno WHERE lineqty > 1; 

itemname	lineqty
Safari chair	50
Hammock	50
Tent - 8 person	8

3c.

Report all items of type “F” that have been sold.

SELECT itemname FROM item
  WHERE itemtype = 'F'
  AND EXISTS 
   (SELECT * FROM lineitem
   WHERE lineitem.itemno = item.itemno);

SELECT DISTINCT(itemname) FROM item JOIN lineitem
  ON item.itemno = lineitem.itemno
  WHERE itemtype = 'F';

itemname
Hammock
Safari chair
Tent - 2 person
Tent - 8 person

3e.

Compute the total value of each sale.

SELECT saleno, SUM(lineqty * lineprice) 
FROM lineitem 
GROUP BY saleno;

saleno	sum
1	4.50
2	72.25
3	502.25
4	712.25
5	5109.00

4.

Why do you have to create a third entity when you have an m:m relationship?

One has to create a third entity to store facts about the m:m relationship.

5.

What does a plus sign near a relationship arc mean?

The plus sign near a relationship arc signifies that the identifier of the one-end of the relationship is part of the identifier for the entity at the many-end.

7.

Answer the following queries based on the described relational database.

7a.

List the phone numbers of donors with last names Hays or Jefts.

SELECT dphone FROM donor 
WHERE dlname = 'Hays' OR dlname = 'Jefts';; 

dphone
1352
8103

7c.

How many people made donations in 1999?

SELECT COUNT(donorno) FROM gift 
WHERE year = 1999;

expr1000
10

7e.

What was the total amount donated in 2000?

SELECT SUM (amount) FROM gift 
WHERE year = 2000;

expr1000
$6,768.00

7g.

List the donors whose average donation is more than twice the average donation of all donors.

SELECT dlname, dfname, AVG(amount) 
FROM donor JOIN gift 
ON donor.donorno = gift.donorno 
GROUP BY dlname, dfname 
HAVING AVG(amount) > (SELECT 2*AVG(amount) FROM gift);

dfname	dlname	expr1002
Gulsen	Beckman	$2,405.00

7i.

Report the total donations in 2001 by state.

SELECT DSTATE, SUM(amount) 
FROM donor JOIN gift 
ON donor.donorno = gift.donorno
WHERE year = 2001 GROUP BY dstate;

dstate	expr1001
AK	$332.00
AZ	$155.00
GA	$671.00
MA	$499.00
MN	$835.00
NC	$966.00
ND	$345.00
WA	$5,208.00